﻿IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'OrdenTrabajoSearch')
	BEGIN
		DROP  Procedure  [OrdenTrabajoSearch]
	END
GO


CREATE PROCEDURE [dbo].[OrdenTrabajoSearch]
	-- Add the parameters for the stored procedure here
	@work_order_number VARCHAR(30) = NULL, 
	@work_order_status VARCHAR(1) = NULL,
	@work_order_begin_date DATETIME = NULL,
	@work_order_end_date DATETIME = NULL,
	@emp_id INT
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	SELECT	ot.Id,
			ot.NumeroOT,
			ot.FechaInicial, 
			ot.FechaFinal, 
			ot.ClienteId, 
			c.Cliente_Nombre as ClienteNombre, 
			ot.ArticuloId, 
			a.Art_Nombre as ArticuloNombre, 
			ot.UnidadesSolicitadas, 
			ot.UnidadesProducidas, 
			ot.Estado,
			CASE ot.Estado
                WHEN 'A' THEN 'EN PROCESO'
                WHEN 'T' THEN 'FINALIZADA'
			END AS EstadoNombre
	FROM Lit_OrdenTrabajo ot
	LEFT JOIN Cliente c ON ot.ClienteId = c.Cliente_Id AND ot.EmpresaId = c.Emp_Id
	LEFT JOIN Articulo a ON ot.ArticuloId = a.Art_Id AND ot.EmpresaId = a.Emp_Id
	WHERE ot.NumeroOT = ISNULL(@work_order_number, ot.NumeroOT)
	AND ot.Estado = ISNULL(@work_order_status, ot.Estado)
	AND ot.FechaInicial BETWEEN ISNULL(@work_order_begin_date, ot.FechaInicial) AND ISNULL(@work_order_end_date, ot.FechaInicial)
	AND ot.EmpresaId = @emp_id;
END
GO
